<?php

/**
* @copyright   (C) 2010 iJoomla, Inc. - All rights reserved.
* @license  GNU General Public License, version 2 (http://www.gnu.org/licenses/gpl-2.0.html) 
* @author  iJoomla.com webmaster@ijoomla.com
* @url   http://www.ijoomla.com/licensing/
* the PHP code portions are distributed under the GPL license. If not otherwise stated, all images, manuals, cascading style sheets, and included JavaScript  
* are NOT GPL, and are released under the IJOOMLA Proprietary Use License v1.0 
* More info at http://www.ijoomla.com/licensing/
*/

defined('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.modellist');
jimport('joomla.utilities.date');

class iJoomla_SurveysModelSurveys extends JModelList{
	
	protected $_context = 'com_surveys.surveys';
	private $total=0;

	function populateState(){
		// Initialize variables.
		$app = JFactory::getApplication('administrator');
		// Load the list state.
		$this->setState('list.start', $app->getUserStateFromRequest($this->_context . '.list.start', 'limitstart', 0, 'int'));
		$this->setState('list.limit', $app->getUserStateFromRequest($this->_context . '.list.limit', 'limit', $app->getCfg('list_limit', 25) , 'int'));
		$this->setState('selected', JRequest::getVar('cid', array()));		
	}
	
	function getPagination(){
		$pagination=parent::getPagination();
		$pagination->total=$this->total;
		if($pagination->total%$pagination->limit>0){
			$nr_pages=intval($pagination->total/$pagination->limit)+1;
		}	
		else{ 
			$nr_pages=intval($pagination->total/$pagination->limit);
		}	
		$pagination->set('pages.total',$nr_pages);
		$pagination->set('pages.stop',$nr_pages);		
		
		return $pagination;
	}
	
	function getItems(){
		$config = new JConfig();	
		$app = JFactory::getApplication('administrator');
		$limistart = $app->getUserStateFromRequest($this->context.'.list.start', 'limitstart');
		$limit = $app->getUserStateFromRequest($this->context.'.list.limit', 'limit', $config->list_limit);

		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query = $this->getListQuery();
		
		$db->setQuery($query);
		$db->query();
		$result	= $db->loadObjectList();
		$this->total=count($result);
		
		$db->setQuery($query,$limistart,$limit);
		$db->query();
		$result	= $db->loadObjectList();
		
		return $result;
	}
	
	function getListQuery(){		
		$database	= JFactory::getDBO();
		$query		= $database->getQuery(true);
		$app 		= JFactory::getApplication('administrator');

		$filter_search = $app->getUserStateFromRequest($this->context.'.filter.search', 'search','','string');
		$this->setState('filter.search', $filter_search, 'string');
		
		$where = " 1=1 ";
		
		if($filter_search != ""){
			$where .= " and title like '%".addslashes($filter_search)."%' ";
		}
		
		$query->select('*');
		$query->from('#__ijoomla_surveys_surveys');
		$query->where($where);
		$query->order("ordering asc");	
							
		return $query;		
	}
	
	function publish_unpublish(){
		$task = JRequest::getVar("task", "");
		$value = "";
		$ids = JRequest::getVar("cid");
		if($task == "publish"){
			$value = "1";
		}
		else{
			$value = "0";
		}
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		foreach($ids as $key=>$id){
			$query->clear();
			$query->update("#__ijoomla_surveys_surveys");
			$query->set("published=".$value);
			$query->where("id=".$id);
			$db->setQuery($query);
			if(!$db->query()){				
				return false;
			}
		}
		return true;
	}
	
	function remove(){
		$ids = JRequest::getVar("cid");
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		foreach($ids as $key=>$id){
			$query->clear();
			$query->delete("#__ijoomla_surveys_surveys");
			$query->where("id=".$id);
			$db->setQuery($query);
			if(!$db->query()){				
				return false;
			}
		}
		return true;
	}
	
	function saveorder(){
		$ids = JRequest::getVar("cid");
		$orders = JRequest::getVar("order");
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		foreach($ids as $key=>$id){
			$query->clear();
			$query->update("#__ijoomla_surveys_surveys");
			$query->set("ordering=".$orders[$id]);
			$query->where("id=".$id);
			$db->setQuery($query);
			if(!$db->query()){				
				return false;
			}
		}
		return true;
	}
	
	function orderup(){
		$db =& JFactory::getDBO();
		$ids = JRequest::getVar("cid");
		$table =& $this->getTable();
		$table->load($ids["0"]);
		if(!$table->move(-1)){
			return false;
		}
		return true;
	}
	
	function orderdown(){
		$db =& JFactory::getDBO();
		$ids = JRequest::getVar("cid");
		$table =& $this->getTable();
		$table->load($ids["0"]);		
		if(!$table->move(1)){
			return false;
		}
		return true;
	}
	
	function getResponses($id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('count(*)');
		$query->from('#__ijoomla_surveys_session');
		$query->where("survey_id=".$id." and completed=1 and last_page_id=0 and published=1");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadResult();
		return $result;
	}
	
	function getPages($id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('count(*)');
		$query->from('#__ijoomla_surveys_pages');
		$query->where("survey_id=".$id."  and published=1");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadResult();
		return $result;
	}
	
	function getQuestions($id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('count(*)');
		$query->from('#__ijoomla_surveys_questions');
		$query->where("survey_id=".$id." and published=1");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadResult();
		return $result;
	}
	
	function showresults(){
		$task = JRequest::getVar("task", "");
		$value = "";
		$ids = JRequest::getVar("cid");
		if($task == "showresults"){
			$value = "1";
		}
		else{
			$value = "0";
		}
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		foreach($ids as $key=>$id){
			$query->clear();
			$query->update("#__ijoomla_surveys_surveys");
			$query->set("show_result =".$value);
			$query->where("id=".$id);
			$db->setQuery($query);
			if(!$db->query()){				
				return false;
			}
		}
		return true;
	}
	
	function access(){
		$task = JRequest::getVar("task", "");
		$value = "";
		$ids = JRequest::getVar("cid");
		if($task == "accessregistered"){
			$value = "2";
		}
		elseif($task == "accesspublic"){
			$value = "1";
		}
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		foreach($ids as $key=>$id){
			$query->clear();
			$query->update("#__ijoomla_surveys_surveys");
			$query->set("access=".$value);
			$query->where("id=".$id);
			$db->setQuery($query);
			if(!$db->query()){				
				return false;
			}
		}
		return true;
	}
	
	function getMaxOrderingSurvey(){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('max(ordering)');
		$query->from('#__ijoomla_surveys_surveys');
		$db->setQuery($query);
		$db->query();
		$result = $db->loadResult();
		return $result;
	}
	
	function getMaxOrderingPages($s_id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('max(ordering)');
		$query->from('#__ijoomla_surveys_pages');
		$query->where('survey_id='.$s_id);
		$db->setQuery($query);
		$db->query();
		$result = $db->loadResult();
		return $result;
	}
	
	function getMaxOrderingQuestion($s_id, $p_id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('max(ordering)');
		$query->from('#__ijoomla_surveys_questions');
		$query->where('survey_id='.$s_id." and page_id=".$p_id);
		$db->setQuery($query);
		$db->query();
		$result = $db->loadResult();
		return $result;
	}
	
	function getSurveyValues($s_id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('*');
		$query->from('#__ijoomla_surveys_surveys');
		$query->where("id=".$s_id);
		$db->setQuery($query);
		$db->query();
		$result = $db->loadAssocList();
		return $result;
	}
	
	function getTitlesSurvey($title){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('title');
		$query->from('#__ijoomla_surveys_surveys');
		$query->where("title like '%".addslashes($title)."%'");
		$query->order("id desc");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadAssocList();
		if($result != NULL && is_array($result) && count($result)>0){
			return $result["0"]["title"];
		}
		return $title;
	}
	
	function getTitlesPage($title){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('title');
		$query->from('#__ijoomla_surveys_pages');
		$query->where("title like '%".addslashes($title)."%'");
		$query->order("id desc");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadAssocList();
		if($result != NULL && is_array($result) && count($result)>0){
			return $result["0"]["title"];
		}
		return $title;
	}
	
	function getTitlesSkip($title){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('title');
		$query->from('#__ijoomla_surveys_skip_logics');
		$query->where("title like '%".addslashes($title)."%'");
		$query->order("id desc");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadAssocList();
		if($result != NULL && is_array($result) && count($result)>0){
			return $result["0"]["title"];
		}
		return $title;
	}
	
	function getPageIdForSkipAction($new_survey_id, $title){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('id');
		$query->from('#__ijoomla_surveys_pages');
		$query->where("title like '%".addslashes($title)."%' and survey_id=".$new_survey_id);
		$query->order("id desc");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadAssocList();		
		if($result != NULL && is_array($result) && count($result)>0){
			return $result["0"]["id"];
		}
		return "";
	}
	
	function getTitlesQuestion($title){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('title');
		$query->from('#__ijoomla_surveys_questions');
		$query->where("title like '%".addslashes($title)."%'");
		$query->order("id desc");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadAssocList();
		if($result != NULL && is_array($result) && count($result)>0){
			return $result["0"]["title"];
		}
		return $title;
	}
	
	function getSurveyPages($s_id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('*');
		$query->from('#__ijoomla_surveys_pages');
		$query->where("survey_id=".$s_id);
		$query->order("ordering asc");
		$db->setQuery($query);
		$db->query();
		$result = $db->loadAssocList();
		return $result;
	}
	
	function getSurveyQuestions($s_id, $p_id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('*');
		$query->from('#__ijoomla_surveys_questions');
		$query->where("survey_id=".$s_id." and page_id=".$p_id);
		$query->order("ordering asc");
		$db->setQuery($query);
		$db->query();		
		$result = $db->loadAssocList();
		return $result;
	}
	
	function copyColumnsAnsersQuestion($old_question_id, $new_question_id){
		$db =& JFactory::getDBO();
		$sql = "select value from #__ijoomla_surveys_answers where question_id=".$old_question_id;
		$db->setQuery($sql);
		$db->query();
		$result = $db->loadAssocList();
		foreach($result as $key=>$value){
			$sql = "insert into #__ijoomla_surveys_answers(`question_id`, `value`) values (".$new_question_id.", '".$value["value"]."')";
			$db->setQuery($sql);
			$db->query();
		}
		
		$sql = "select count(*) from #__ijoomla_surveys_menu_heading where question_id=".$old_question_id;
		$db->setQuery($sql);
		$db->query();
		$result = $db->loadResult();
				
		if($result != "0"){
			$sql = "select * from #__ijoomla_surveys_menu_heading where question_id=".$old_question_id;
			$db->setQuery($sql);
			$db->query();
			$result = $db->loadAssocList();
			foreach($result as $key=>$value){
				$sql = "insert into #__ijoomla_surveys_menu_heading (`question_id`, `value`, `order`) values (".$new_question_id.", '".$value["value"]."', ".$value["order"].")";
				$db->setQuery($sql);
				$db->query();
				
				$sql = "select id from #__ijoomla_surveys_menu_heading where question_id=".$new_question_id." and value='".$value["value"]."'";
				$db->setQuery($sql);
				$db->query();				
				$new_id = $db->loadResult();
				
				$sql = "select * from #__ijoomla_surveys_answer_columns where menu_order=".$value["id"];
				$db->setQuery($sql);
				$db->query();
				$an_columns = $db->loadAssocList();
				foreach($an_columns as $ac_key=>$ac_value){
					$sql = "insert into #__ijoomla_surveys_answer_columns (`question_id`, `value`, `menu_order`) values (".$new_question_id.", '".$ac_value["value"]."', ".$new_id.")";
					$db->setQuery($sql);
					$db->query();
				}
			}
		}
		else{
			$sql = "select * from #__ijoomla_surveys_answer_columns where question_id=".$old_question_id;
			$db->setQuery($sql);
			$db->query();
			$an_columns = $db->loadAssocList();
			foreach($an_columns as $ac_key=>$ac_value){
				$sql = "insert into #__ijoomla_surveys_answer_columns (`question_id`, `value`, `menu_order`) values (".$new_question_id.", '".trim($ac_value["value"])."', 0)";
				$db->setQuery($sql);
				$db->query();
			}
		}		
	}
	
	function getSkipActions($s_id, $old_question_id){
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		$query->select('*');
		$query->from('#__ijoomla_surveys_skip_logics');
		$query->where("survey_id=".$s_id." and question_id=".$old_question_id);
		$query->order("ordering asc");
		$db->setQuery($query);
		$db->query();		
		$result = $db->loadAssocList();
		return $result;
	}
	
	function copySurvey(){
		$db =& JFactory::getDBO();
		$ids = JRequest::getVar("cid");
		$table =& $this->getTable();
		$table_page =& $this->getTable("newpage");
		$table_question =& $this->getTable("newquestion");
		$max_ordering_survey = $this->getMaxOrderingSurvey();
		
		foreach($ids as $s_key=>$s_value){
			// copy surveys ------------
			$nr = 1;
			$s_id = $s_value;			
			$survey_values = $this->getSurveyValues($s_id);			
			$last_title = $this->getTitlesSurvey($survey_values["0"]["title"]);	
			if(strpos($last_title, "Copy") != false){
				$nr = intval(substr(trim($last_title), -1)) + 1; 
			}
			$survey_values["0"]["title"] = $survey_values["0"]["title"]." Copy ".$nr;
			$survey_values["0"]["ordering"] = $max_ordering_survey + 1;
			$max_ordering_survey ++;
			$alias = strtolower($survey_values["0"]["title"]);
			$alias = str_replace(" ", "-", $alias);
			$alias = str_replace("&", "a", $alias);
			$alias = str_replace("@", "", $alias);
			$alias = str_replace("'", "", $alias);
			$alias = str_replace('"', "", $alias);
			$alias = str_replace('?', "", $alias);
			$alias = str_replace('/', "", $alias);
			$alias = str_replace('\\', "", $alias);
			$survey_values["0"]["alias"] = $alias;
			unset($survey_values["0"]["id"]);
			
			if (!$table->bind($survey_values["0"])){
				echo $this->_db->getErrorMsg(); exit;
				$this->setError($this->_db->getErrorMsg());
				$return["0"] = false;
			}
			// Make sure the news record is valid
			if (!$table->check()){
				echo $this->_db->getErrorMsg(); exit;		
				$this->setError($this->_db->getErrorMsg());
				$return["0"] = false;
			}		
			// Store the web link table to the database
			if (!$table->store()){
				echo $this->_db->getErrorMsg(); exit;
				$this->setError( $this->_db->getErrorMsg() );
				$return["0"] = false;
			}
			
			$new_survey_id = $table->id;
			// -------------------------
			// copy pages --------------
			$pages = $this->getSurveyPages($s_id);						
			$max_ordering_pages = $this->getMaxOrderingPages($s_id);
			
			foreach($pages as $p_key=>$p_value){
				$nr = 1;
				$page_title = $this->getTitlesPage($p_value["title"]);
				
				if(strpos($page_title, "Copy") != false){
					$nr = intval(substr(trim($page_title), -1)) + 1; 
				}
				$p_value["title"] = $p_value["title"]." Copy ".$nr;
				$p_value["ordering"] = $max_ordering_pages + 1;
				$p_value["survey_id"] = $new_survey_id;
				$max_ordering_pages ++;
				$old_page_id = $p_value["id"];
				unset($p_value["id"]);
				unset($table_page->id);
				if (!$table_page->bind($p_value)){
					echo $this->_db->getErrorMsg(); exit;
					$this->setError($this->_db->getErrorMsg());
					$return["0"] = false;
				}
				// Make sure the news record is valid
				if (!$table_page->check()){
					echo $this->_db->getErrorMsg(); exit;		
					$this->setError($this->_db->getErrorMsg());
					$return["0"] = false;
				}		
				// Store the web link table to the database
				if (!$table_page->store()){
					echo $this->_db->getErrorMsg(); exit;
					$this->setError( $this->_db->getErrorMsg() );
					$return["0"] = false;
				}
				$new_page_id = $table_page->id;	
				// copy questions --------------
				$questions = $this->getSurveyQuestions($s_id, $old_page_id);
				$max_ordering_question = $this->getMaxOrderingQuestion($s_id, $old_page_id);
				
				foreach($questions as $q_key=>$q_value){
					$nr = 1;
					$question_title = $this->getTitlesQuestion($q_value["title"]);
					if(strpos($question_title, "Copy") != false){
						$nr = intval(substr(trim($question_title), -1)) + 1; 
					}
					$q_value["title"] = $q_value["title"]." Copy ".$nr;
					$q_value["ordering"] = $max_ordering_question + 1;
					$q_value["survey_id"] = $new_survey_id;
					$q_value["page_id"] = $new_page_id;
					$max_ordering_question ++;
					$old_question_id = $q_value["id"];
					unset($q_value["id"]);
					unset($table_question->id);
					
					if (!$table_question->bind($q_value)){
						echo $this->_db->getErrorMsg(); exit;
						$this->setError($this->_db->getErrorMsg());
						$return["0"] = false;
					}
					// Make sure the news record is valid
					if (!$table_question->check()){
						echo $this->_db->getErrorMsg(); exit;		
						$this->setError($this->_db->getErrorMsg());
						$return["0"] = false;
					}		
					// Store the web link table to the database
					if (!$table_question->store()){
						echo $this->_db->getErrorMsg(); exit;
						$this->setError( $this->_db->getErrorMsg() );
						$return["0"] = false;
					}
					$new_question_id = $table_question->id;
					$this->copyColumnsAnsersQuestion($old_question_id, $new_question_id);				
				}
				// -----------------------------
			}// for each pages
			// -------------------------			
		}//foreach
	}
	
	function clearSurvey(){
		$ids = JRequest::getVar("cid");
		$db =& JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->clear();
		foreach($ids as $key=>$id){
			$query->select("id");
			$query->from("#__ijoomla_surveys_session");
			$query->where("survey_id=".$id);
			$db->setQuery($query);
			$db->query();
			$result = $db->loadAssocList();
			$result_id = array();
			
			foreach($result as $key=>$value){
				$result_id[] = $value["id"];
			}
			
			$query->clear();
			$query->delete("#__ijoomla_surveys_session");
			$query->where("survey_id=".$id);
			$db->setQuery($query);
			if($db->query()){				
				$query->clear();
				$query->delete("#__ijoomla_surveys_result");
				$query->where("session_id in (".implode(",", $result_id).")");
				$db->setQuery($query);	
				$db->query();
				
				$query->clear();
				$query->delete("#__ijoomla_surveys_result_text");
				$query->where("session_id in (".implode(",", $result_id).")");
				$db->setQuery($query);	
				$db->query();		
			}
			else{
				return false;
			}
		}
		return true;
	}
}

?>